package com.ipan.builder.db.comment;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import org.apache.commons.lang.StringUtils;
import org.hibernate.mapping.Column;
import org.hibernate.mapping.Table;
import org.hibernate.tool.ipan.reveng.IDatabaseReverser;

import com.ipan.builder.db.DatabaseOp;
import com.ipan.builder.entity.JpaColumnCfg;
import com.ipan.builder.entity.JpaEntityCfg;

/**
 * Mysql更新数据库注释实现
 * 
 * @author iPan
 * @version 2013-12-7
 */
public class MysqlCommentOp extends BaseCommentOp {
	
	// 更新表注释
	private static final String UPDATE_TABLE_SQL = "alter table {table} comment '{comment}'";
	// 更新字段注释
	private static final String UPDATE_COLUMN_SQL = "alter table {table} modify column {table}.{column} {type} {auto} comment '{comment}'";
	
	private static final String SELECT_COLUMN_SQL = "select COLUMN_TYPE from information_schema.COLUMNS";
	private static final String SELECT_COLUMN_TABLE_SQL = SELECT_COLUMN_SQL + " where TABLE_NAME = ? and COLUMN_NAME = ?";
	private static final String SELECT_COLUMN_SCHEMA_SQL = SELECT_COLUMN_SQL + " where TABLE_SCHEMA = ? and TABLE_NAME = ? and COLUMN_NAME = ?";

	public MysqlCommentOp(DatabaseOp databaseOp) {
		super(databaseOp);
	}

	public void updateTableComment(JpaEntityCfg entityCfg) {
		String tableName = entityCfg.getTableName();
		String tableComment = entityCfg.getTableComment();
		String sql = null;
		sql = this.fillSql(UPDATE_TABLE_SQL, SQL_TABLE, tableName);
		sql = this.fillSql(sql, SQL_COMMENT, tableComment);
		databaseOp.execute(sql, null);
	}

	public void updateColumnComment(JpaEntityCfg entityCfg) {
		String tableName = entityCfg.getTableName().toLowerCase();
		IDatabaseReverser reverser = databaseOp.getReverser();
		Table table = reverser.getTable(tableName);
		if (table == null) {
			return ;
		}
		
		Connection conn = null;
		Statement statement = null;
		String baseSql = this.fillSql(UPDATE_COLUMN_SQL, SQL_TABLE, tableName);
		String sql = "";
		try {
			conn = databaseOp.getConnection();
			statement = conn.createStatement();
			Iterator<?> iter = table.getColumnIterator();
			while (iter.hasNext()) {
				Column column = (Column) iter.next();
				String name = column.getName().toLowerCase();
				String sqlType = column.getSqlType().toLowerCase();
				int precision = column.getPrecision();
				int scale = column.getScale();
				JpaColumnCfg colCfg = entityCfg.getColumnCfgByName(name);
				if (colCfg.isTransient() || colCfg.isAssociateColumn()) { // 排除Transient以及关联字段
					continue;
				}
				String comment = (colCfg != null) ? colCfg.getComment() : "";
				String type = null;
				try {
					type = getType(tableName, name, sqlType, precision, scale);
				} catch (Exception e) {
					log.warn("获取字段类型（{}.{}）出错.", tableName, name);
					continue;
				}
				sql = this.fillSql(baseSql, SQL_COLUMN, name);
				sql = this.fillSql(sql, SQL_COMMENT, comment);
				if ("id".equals(name)) { // 主键ID
					sql = this.fillSql(sql, SQL_AUTO, "AUTO_INCREMENT");
				} else { // 其他字段
					sql = this.fillSql(sql, SQL_AUTO, "");
				}
				sql = this.fillSql(sql, SQL_TYPE, type);
				log.info(sql);
				statement.execute(sql);
			}
		} catch (SQLException e) {
			databaseOp.throwJDBCException(e, sql, null);
		} finally {
			databaseOp.closeStatement(statement);
			databaseOp.closeConnection(conn);
		}
	}
	
	private String getType(String table, String column, String type, int precision, int scale) {
		String result = type;
		if ("tinyint".equals(type)) {
			result = "tinyint(" + precision + ")";
		} else if ("smallint".equals(type)) {
			result = "smallint(" + precision + ")";
		} else if ("mediumint".equals(type)) {
			result = "mediumint(" + precision + ")";
		} else if ("int".equals(type)) {
			result = "int(" + precision + ")";
		} else if ("integer".equals(type)) {
			result = "integer(" + precision + ")";
		} else if ("bigint".equals(type)) {
			result = "bigint(" + precision + ")";
		} else if ("real".equals(type)) {
			result = "real(" + precision + ", " + scale + ")";
		} else if ("double".equals(type)) {
			result = "double(" + precision + ", " + scale + ")";
		} else if ("float".equals(type)) {
			result = "float(" + precision + ", " + scale + ")";
		} else if ("decimal".equals(type)) {
			result = "decimal(" + precision + ", " + scale + ")";
		} else if ("numeric".equals(type)) {
			result = "numeric(" + precision + ", " + scale + ")";
//		} else if ("date".equals(type)) {
//		} else if ("time".equals(type)) {
//		} else if ("timestamp".equals(type)) {
//		} else if ("datetime".equals(type)) {
		} else if ("char".equals(type)) {
			result = "char(" + precision + ")";
		} else if ("varchar".equals(type)) {
			result = "varchar(" + precision + ")"; 
		} else if ("varbinary".equals(type)) {
			result = "varbinary(" + precision + ")";
		} else if ("binary".equals(type)) {
			result = "binary(" + precision + ")";
//		} else if ("tinyblob".equals(type)) {
//		} else if ("blob".equals(type)) {
//		} else if ("mediumblob".equals(type)) {
//		} else if ("longblob".equals(type)) {
//		} else if ("tinytext".equals(type)) {
//		} else if ("text".equals(type)) {
//		} else if ("mediumtext".equals(type)) {
//		} else if ("longtext".equals(type)) {
		} else if ("enum".equals(type)) {
			result = getColumnType(table, column);
		} else if ("set".equals(type)) {
			result = getColumnType(table, column);
		}
		
		return result;
	}
	
	// enum, set类型需要获取配置项
	private String getColumnType(String table, String name) {
		String schema = this.getSchemaName();
		String sql = null;
		Connection conn = null;
		PreparedStatement statement = null;
		ResultSet resultSet = null;
		String result = null;
		
		conn = databaseOp.getConnection();
		List<String> params = new ArrayList<String>();
		if (StringUtils.isEmpty(schema)) {
			sql = SELECT_COLUMN_TABLE_SQL;
			params.add(table);
			params.add(name);
		} else {
			sql = SELECT_COLUMN_SCHEMA_SQL;
			params.add(schema);
			params.add(table);
			params.add(name);
		}
		
		try {
			statement = conn.prepareStatement(sql);
			for (int i=1, size=params.size(); i<=size; ++i) {
				statement.setString(i, params.get(i -1));
			}
			resultSet = statement.executeQuery();
			if (resultSet.next()) {
				result = resultSet.getString(1);
			}
		} catch (SQLException e) {
			databaseOp.throwJDBCException(e, sql, params.toArray());
		} finally {
			databaseOp.closeResultSet(resultSet);
			databaseOp.closeStatement(statement);
			databaseOp.closeConnection(conn);
		}
		
		return result;
	}
	
}
//alter table {table} modify column {field} {type} comment '{comment}'
//type:
//    TINYINT[(length)] [UNSIGNED] [ZEROFILL]
//  | SMALLINT[(length)] [UNSIGNED] [ZEROFILL]
//  | MEDIUMINT[(length)] [UNSIGNED] [ZEROFILL]
//  | INT[(length)] [UNSIGNED] [ZEROFILL]
//  | INTEGER[(length)] [UNSIGNED] [ZEROFILL]
//  | BIGINT[(length)] [UNSIGNED] [ZEROFILL]
//  | REAL[(length,decimals)] [UNSIGNED] [ZEROFILL]
//  | DOUBLE[(length,decimals)] [UNSIGNED] [ZEROFILL]
//  | FLOAT[(length,decimals)] [UNSIGNED] [ZEROFILL]
//  | DECIMAL(length,decimals) [UNSIGNED] [ZEROFILL]
//  | NUMERIC(length,decimals) [UNSIGNED] [ZEROFILL]
//  | DATE
//  | TIME
//  | TIMESTAMP
//  | DATETIME
//  | CHAR(length) [BINARY | ASCII | UNICODE]
//  | VARCHAR(length) [BINARY]
//  | TINYBLOB
//  | BLOB
//  | MEDIUMBLOB
//  | LONGBLOB
//  | TINYTEXT [BINARY]
//  | TEXT [BINARY]
//  | MEDIUMTEXT [BINARY]
//  | LONGTEXT [BINARY]
//  | ENUM(value1,value2,value3,...)
//  | SET(value1,value2,value3,...)
//  | spatial_type
